package net.suncaper.hotel.mapper.impl;

import net.suncaper.hotel.common.utils.JDBCUtils;
import net.suncaper.hotel.mapper.ICheckInMapper;
import net.suncaper.hotel.model.domain.CheckIn;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.math.BigDecimal;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;

/**
 * 入住登记数据访问实现类
 */
public class CheckInMapperImpl implements ICheckInMapper {
    private QueryRunner queryRunner = new QueryRunner();

    @Override
    public CheckIn findById(Integer id) {
        String sql = "SELECT * FROM check_in WHERE id = ?";
        try {
            return queryRunner.query(JDBCUtils.getConnection(), sql,
                new BeanHandler<>(CheckIn.class), id);
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    @Override
    public CheckIn findByReservationId(Integer reservationId) {
        String sql = "SELECT * FROM check_in WHERE reservation_id = ?";
        try {
            return queryRunner.query(JDBCUtils.getConnection(), sql,
                new BeanHandler<>(CheckIn.class), reservationId);
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    @Override
    public CheckIn findCurrentByRoomId(Integer roomId) {
        String sql = "SELECT * FROM check_in WHERE room_id = ? AND status = 'CHECKED_IN'";
        try {
            return queryRunner.query(JDBCUtils.getConnection(), sql,
                new BeanHandler<>(CheckIn.class), roomId);
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    @Override
    public List<CheckIn> findAll() {
        String sql = "SELECT * FROM check_in ORDER BY create_time DESC";
        try {
            return queryRunner.query(JDBCUtils.getConnection(), sql,
                new BeanListHandler<>(CheckIn.class));
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    @Override
    public List<CheckIn> findByStatus(String status) {
        String sql = "SELECT * FROM check_in WHERE status = ? ORDER BY create_time DESC";
        try {
            return queryRunner.query(JDBCUtils.getConnection(), sql,
                new BeanListHandler<>(CheckIn.class), status);
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    @Override
    public List<CheckIn> findTodayCheckIns(Date date) {
        String sql = "SELECT * FROM check_in WHERE DATE(check_in_time) = DATE(?) ORDER BY check_in_time";
        try {
            return queryRunner.query(JDBCUtils.getConnection(), sql,
                new BeanListHandler<>(CheckIn.class), date);
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    @Override
    public List<CheckIn> findTodayCheckOuts(Date date) {
        String sql = "SELECT * FROM check_in WHERE DATE(actual_check_out_time) = DATE(?) " +
                     "OR (status = 'CHECKED_IN' AND DATE(expected_check_out_time) = DATE(?)) " +
                     "ORDER BY expected_check_out_time";
        try {
            return queryRunner.query(JDBCUtils.getConnection(), sql,
                new BeanListHandler<>(CheckIn.class), date, date);
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    @Override
    public List<CheckIn> findByPage(int offset, int pageSize) {
        String sql = "SELECT * FROM check_in ORDER BY create_time DESC LIMIT ?, ?";
        try {
            return queryRunner.query(JDBCUtils.getConnection(), sql,
                new BeanListHandler<>(CheckIn.class), offset, pageSize);
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    @Override
    public long count() {
        String sql = "SELECT COUNT(*) FROM check_in";
        try {
            return queryRunner.query(JDBCUtils.getConnection(), sql,
                new ScalarHandler<Long>());
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    @Override
    public int insert(CheckIn checkIn) {
        String sql = "INSERT INTO check_in (reservation_id, customer_id, room_id, " +
                     "check_in_time, expected_check_out_time, room_rate, deposit, " +
                     "status, operator_id, notes) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        try {
            return queryRunner.update(JDBCUtils.getConnection(), sql,
                checkIn.getReservationId(), checkIn.getCustomerId(), checkIn.getRoomId(),
                checkIn.getCheckInTime(), checkIn.getExpectedCheckOutTime(),
                checkIn.getRoomRate(), checkIn.getDeposit(), checkIn.getStatus(),
                checkIn.getOperatorId(), checkIn.getNotes());
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    @Override
    public int update(CheckIn checkIn) {
        String sql = "UPDATE check_in SET expected_check_out_time = ?, room_rate = ?, " +
                     "deposit = ?, notes = ? WHERE id = ?";
        try {
            return queryRunner.update(JDBCUtils.getConnection(), sql,
                checkIn.getExpectedCheckOutTime(), checkIn.getRoomRate(),
                checkIn.getDeposit(), checkIn.getNotes(), checkIn.getId());
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    @Override
    public int checkOut(Integer id, Date checkOutTime, BigDecimal totalAmount) {
        String sql = "UPDATE check_in SET actual_check_out_time = ?, total_amount = ?, " +
                     "status = 'CHECKED_OUT' WHERE id = ?";
        try {
            return queryRunner.update(JDBCUtils.getConnection(), sql,
                checkOutTime, totalAmount, id);
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }
}